package com.beiming.libra.common.utils;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

/**
 * 使用sax解析excel文件
 *
 * @author tp Date : 2018/6/13/013 15:02
 */
public class ExcelXlsxReader {

  /**
   * 解析excel
   */
  private List<List<Object>> allValueList = new LinkedList<>();

  public void processOneSheet(String filename) throws Exception {
    InputStream sheet = null;
    OPCPackage pkg = null;
    try {
      pkg = OPCPackage.open(filename);
      XSSFReader r = new XSSFReader(pkg);
      SharedStringsTable sst = r.getSharedStringsTable();
      XMLReader parser = fetchSheetParser(sst);
      sheet = r.getSheet("rId1");
      InputSource sheetSource = new InputSource(sheet);
      parser.parse(sheetSource);
    } catch (Exception e) {
      throw e;
    } finally {
      if (sheet != null) {
        sheet.close();
      }
      if (pkg != null) {
        pkg.close();
      }
    }
  }

  private XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
    XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
    ContentHandler handler = new SheetHandler(sst);
    parser.setContentHandler(handler);
    return parser;
  }

  public List<List<Object>> excelReader(String fileName) {
    ExcelXlsxReader eventModel = new ExcelXlsxReader();
    try {
      eventModel.processOneSheet(fileName);
    } catch (Exception e) {
      e.printStackTrace();
    }
    return eventModel.getAllValueList();
  }

  public List<List<Object>> getAllValueList() {
    return allValueList;
  }

  private class SheetHandler extends DefaultHandler {

    private SharedStringsTable sst;
    private String lastContents;
    private boolean isString;
    private boolean validRow;
    /**
     * rowValueList
     */
    private List<Object> rowValueList;

    private SheetHandler(SharedStringsTable sst) {
      this.sst = sst;
    }

    public void startElement(String uri, String localName, String name, Attributes attributes)
        throws SAXException {
      if ("row".equals(name) || "c".equals(name)) {
        String row = attributes.getValue("r");
        int column = getColumn(attributes);
        validRow = true;
        if ("row".equals(name)) {
          rowValueList = new ArrayList<>();
          allValueList.add(rowValueList);
        }
        String cellType = attributes.getValue("t");
        if (cellType != null && "s".equals(cellType)) {
          isString = true;
        } else {
          isString = false;
        }
      }
      lastContents = "";
    }

    public void endElement(String uri, String localName, String name) throws SAXException {
      if (!validRow) {
        return;
      }
      if (isString) {
        int idx = Integer.parseInt(lastContents);
        lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
        isString = false;
        validRow = false;
        if ("v".equals(name)) {
          rowValueList.add(lastContents);
        }
      } else {
        if ("c".equals(name)) {
          rowValueList.add(lastContents);
        }
      }
    }

    public void characters(char[] ch, int start, int length) throws SAXException {
      lastContents += new String(ch, start, length);
    }

    private int getColumn(Attributes attributes) {
      String row = attributes.getValue("r");
      int firstDigit = -1;
      for (int c = 0; c < row.length(); ++c) {
        if (Character.isDigit(row.charAt(c))) {
          firstDigit = c;
          break;
        }
      }
      return Integer.valueOf(row.substring(firstDigit, row.length()));
    }
  }
}
